const db = require('../../db/db.js')
const { checkIdExists } = require('../../utils/sqlUtils.js')


// 新建订单
exports.addOrder = (request, response) => {
  const data = request.body;

  // 验证外键
  const checkUserSql = 'SELECT id FROM users WHERE id = ?';
  const checkUserData = [data.user_id];

  db.query(checkUserSql, checkUserData, (err, res) => {
    if (err) {
      return response.status(500).send({ code: 500, message: '数据库错误', error: err });
    }
    if (res.length === 0) {
      return response.status(400).send({ code: 400, message: '无效的用户ID' });
    }

    // 检查商品是否存在
    const checkGoodsSql = 'SELECT id FROM goods WHERE id = ?';
    const checkGoodsData = [data.goods_id];

    db.query(checkGoodsSql, checkGoodsData, (err, res) => {
      if (err) {
        return response.status(500).send({ code: 500, message: '数据库错误', error: err });
      }
      if (res.length === 0) {
        return response.status(400).send({ code: 400, message: '无效的商品ID' });
      }

      // 插入订单
      const insertSql = 'INSERT INTO orders SET ?';
      const orderData = {
        user_id: data.user_id,
        goods_id: data.goods_id,
        order_status: data.order_status || 0,
        payment_method: data.payment_method || 0,
        payment_status: data.payment_status || 0,
        shipping_method: data.shipping_method || 0,
        price: data.price,
        quantity: data.quantity,
        address: data.address,
        is_delivery: data.is_delivery || 0,
        delivery_time: data.delivery_time || new Date(),
        delivery_address: data.delivery_address
      };

      db.query(insertSql, orderData, (err, result) => {
        if (err) {
          return response.status(500).send({ code: 500, message: '订单创建失败', error: err });
        }
        return response.status(200).send({ code: 200, message: '订单创建成功', orderId: result.insertId });
      });
    });
  });
};

// 获取订单
exports.getOrders = (request, response) => {
  let id = request.params?.id;
  let user_id = request.query?.user_id;
  let status = request.query?.status;
  let payment_status = request.query?.payment_status;
  let shipping_method = request.query?.shipping_method;
  let is_delivered = request.query?.is_delivered;
  let is_deleted = request.query?.is_deleted;
  let keyword = request.query?.keyword;

  // 分页参数
  let page = parseInt(request.query?.page) || 1; // 默认第1页
  let pageSize = parseInt(request.query?.page_size) || 20; // 默认每页20条记录

  let conditions = [];
  let queryParams = [];

  if (keyword) {
    conditions.push('order_id LIKE ? OR user_id LIKE ?');
    queryParams.push(`%${keyword}%`, `%${keyword}%`);
  }

  // 添加查询条件
  if (id) {
    conditions.push('id = ?');
    queryParams.push(id);
  }
  if (user_id) {
    conditions.push('user_id = ?');
    queryParams.push(user_id);
  }
  if (status) {
    conditions.push('status = ?');
    queryParams.push(status);
  }
  if (payment_status) {
    conditions.push('payment_status = ?');
    queryParams.push(payment_status);
  }
  if (shipping_method) {
    conditions.push('shipping_method = ?');
    queryParams.push(shipping_method);
  }
  if (is_delivered) {
    conditions.push('is_delivered = ?');
    queryParams.push(is_delivered);
  }
  if (is_deleted) {
    conditions.push('is_deleted = ?');
    queryParams.push(is_deleted);
  }

  let whereClause = conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : '';
  let offset = (page - 1) * pageSize;

  // SQL 查询，包括分页
  let sql = `SELECT * FROM orders ${whereClause} LIMIT ? OFFSET ?`;
  queryParams.push(pageSize, offset);

  // 查询数据和总条数
  let countSql = `SELECT COUNT(*) AS total FROM orders ${whereClause}`;

  db.query(sql, queryParams, (err, results) => {
    if (err) {
      return response.status(500).send({
        code: 500,
        message: '查询失败！',
        error: err
      });
    }

    db.query(countSql, queryParams.slice(0, -2), (err, countResult) => {
      if (err) {
        return response.status(500).send({
          code: 500,
          message: '总条数查询失败！',
          error: err
        });
      }

      let total = countResult[0].total;
      let last_page = Math.ceil(total / pageSize);

      response.status(200).send({
        code: 200,
        message: '查询成功！',
        current_page: page,
        last_page: last_page,
        total: total,
        page_size: pageSize,
        data: results
      });
    });
  });
};


// 修改订单
exports.setOrder = async (request, response) => {
  let data = request.body;
  let orderId = request.params?.id;

  if (!orderId) {
    return response.status(400).send({
      code: 400,
      message: '订单ID不能为空。',
    });
  }

  try {
    // 检查user_id、status和shipping_method是否存在
    const user_id = data?.user_id ? await checkIdExists(data.user_id, 'users') : undefined;
    const status = data?.status; // 订单状态通常不需要检查有效性
    const shipping_method = data?.shipping_method; // 运输方式通常不需要检查有效性

    // 更新订单
    const updateSql = 'UPDATE orders SET ? WHERE id = ?';
    const orderData = { ...data, user_id, status, shipping_method };
    db.query(updateSql, [orderData, orderId], (err, result) => {
      if (err) {
        return response.status(500).send({ code: 500, message: '更新失败！', error: err });
      }
      if (result.affectedRows === 0) {
        return response.status(404).send({ code: 404, message: '未找到该订单' });
      }
      return response.status(200).send({ code: 200, message: '订单修改成功！', data: orderData });
    });

  } catch (err) {
    return response.status(500).send({ code: 500, message: '服务器内部错误', error: err });
  }
};
